package rer.persistencia;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import rer.model.Aluno;
import rer.util.ConexaoJDBC_mysql;

public class AlunoDAO implements IAlunoDAO {
	
	private Statement statement;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private String query;
	private Aluno aluno;
	private List<Aluno> alunos;
	private Connection conexao = ConexaoJDBC_mysql.getConnection();
	
	@Override
	public Boolean cadastrar(Aluno aluno) {
		
		try {
			
			query ="INSERT INTO db_ceirr.Aluno (`nomeAluno`, `sexoAluno`, "
					+ "`dobAluno`, `omAluno`, `nomePai`,"
					+ " `nomeMae`, `Cliente_idCliente`, `status`) VALUES "
					+ "(?,?,?,?,?,?,?,?)";
			
			preparedStatement = conexao.prepareStatement(query);
			
			preparedStatement.setString(1, aluno.getNomeAluno());
			preparedStatement.setString(2, aluno.getSexoAluno());
			preparedStatement.setDate(3, new Date(aluno.getDobAluno().getTime()));
			preparedStatement.setString(4, aluno.getOmAluno());
			preparedStatement.setString(5, aluno.getNomePai());
			preparedStatement.setString(6, aluno.getNomeMae());
			preparedStatement.setInt(7, aluno.getIdCliente()); // ??Pegar o id do cliente certo
			preparedStatement.setString(8, aluno.getStatus());
			
			System.out.println("Query foi executada retornando true");
			
			preparedStatement.execute();
			
			return true;
			
		} catch (SQLException e) {
			// TODO: handle exception
			
			System.out.println(e.getMessage());
		}finally{
			try {
				System.out.println("Aqui?");
				preparedStatement.close();
				
			} catch (SQLException e) {
				// TODO: handle exception
				e.printStackTrace();
				System.out.println("Erro ao fechar o statement");
			}
			System.out.println("Finally");
		}
		
		return false;
		
	}

	@Override
	public Boolean editar(Aluno aluno) {
		// TODO Auto-generated method stub
		
		try{
			query = "UPDATE `aluno` SET `nomeAluno`=?, `sexoAluno`=?,`dobAluno`=?,"
					+ "`omAluno`=?,`nomePai`=?,"
					+ "`nomeMae`=?,`Cliente_idCliente`=?,`status`=?"
					+ " WHERE `aluno`.`idAluno` = ?";
			
			preparedStatement = conexao.prepareStatement(query);
			
			preparedStatement.setString(1, aluno.getNomeAluno());
			preparedStatement.setString(2, aluno.getSexoAluno());			
			preparedStatement.setDate(3, new Date(aluno.getDobAluno().getTime()));
			preparedStatement.setString(4, aluno.getOmAluno());
			preparedStatement.setString(5, aluno.getNomePai());
			preparedStatement.setString(6, aluno.getNomeMae());
			preparedStatement.setInt(7, aluno.getIdCliente());
			preparedStatement.setString(8, aluno.getStatus());
			preparedStatement.setInt(9, aluno.getIdAluno());
			
			preparedStatement.executeUpdate();
			
			
			System.out.println("Dados Aluno Alterados com sucesso - DAO ");
			
			return true;

		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		return false;
	}

	@Override
	public Boolean deletar(int id) {
		
		try {
			
			query ="UPDATE `aluno` SET `status`='I' WHERE idAluno = ?";
			
			preparedStatement = conexao.prepareStatement(query);
			
			preparedStatement.setInt(1, id);
			
			preparedStatement.executeUpdate();
			
			System.out.println("Aluno apagado com sucesso!");
			return true;
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			
			return false;
		}
		
		
	}

	@Override
	public List<Aluno> listar() {
		
		alunos = null;
		
		try {
			
			query = "SELECT * FROM db_ceirr.aluno where status = 'A'";
			
			statement = conexao.createStatement();
			resultSet = statement.executeQuery(query);
			
			alunos = new ArrayList<Aluno>();
			
			while(resultSet.next()){
				aluno = new Aluno();
				
				aluno.setIdAluno(resultSet.getInt("idAluno"));
				aluno.setNomeAluno(resultSet.getString("nomeAluno"));
				aluno.setSexoAluno(resultSet.getString("sexoAluno"));
				aluno.setDobAluno(resultSet.getDate("dobAluno"));
				aluno.setOmAluno(resultSet.getString("omAluno"));
				aluno.setNomePai(resultSet.getString("nomePai"));
				aluno.setNomeMae(resultSet.getString("nomeMae"));
				aluno.setIdCliente(resultSet.getInt("Cliente_idCliente"));
				aluno.setStatus(resultSet.getString("status"));
				
				alunos.add(aluno);
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa alunos");
		return alunos;
		
	}
	
	@Override // Estou usando esse metodo ;
	public Aluno pesquisaAluno(int id){
		
		aluno = null;
		
		try {
			
			query = "SELECT * FROM aluno WHERE idAluno = ?";

			preparedStatement = conexao.prepareStatement(query);

			preparedStatement.setInt(1, id);

			resultSet = preparedStatement.executeQuery();

			if (resultSet.next()) {

				aluno = new Aluno();

				aluno.setIdAluno(resultSet.getInt("idAluno"));
				aluno.setNomeAluno(resultSet.getString("nomeAluno"));
				aluno.setSexoAluno(resultSet.getString("sexoAluno"));
				aluno.setDobAluno(resultSet.getDate("dobAluno"));
				aluno.setOmAluno(resultSet.getString("omAluno"));
				aluno.setNomePai(resultSet.getString("nomePai"));
				aluno.setNomeMae(resultSet.getString("nomeMae"));
				aluno.setIdCliente(resultSet.getInt("Cliente_idCliente"));
				aluno.setStatus(resultSet.getString("status"));
				
				
			}
			
			System.out.println("Pesquisa  feita aluno "+ aluno.getIdCliente());
			
		} catch (SQLException e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
		}
		
		return aluno;
	}

	@Override
	public List<Aluno> listarEspera() {
		alunos = null;
		
		try {
			
			query = "SELECT * FROM db_ceirr.aluno where status = 'E'";
			
			statement = conexao.createStatement();
			resultSet = statement.executeQuery(query);
			
			alunos = new ArrayList<Aluno>();
			
			while(resultSet.next()){
				aluno = new Aluno();
				
				aluno.setIdAluno(resultSet.getInt("idAluno"));
				aluno.setNomeAluno(resultSet.getString("nomeAluno"));
				aluno.setSexoAluno(resultSet.getString("sexoAluno"));
				aluno.setDobAluno(resultSet.getDate("dobAluno"));
				aluno.setOmAluno(resultSet.getString("omAluno"));
				aluno.setNomePai(resultSet.getString("nomePai"));
				aluno.setNomeMae(resultSet.getString("nomeMae"));
				aluno.setIdCliente(resultSet.getInt("Cliente_idCliente"));
				aluno.setStatus(resultSet.getString("status"));
				
				alunos.add(aluno);
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa alunos");
		return alunos;
		
	}
	
	@Override
	public List<Aluno> alunoMatricula() {
		
		alunos = null;
		
		try {
			
			query = "SELECT * FROM db_ceirr.aluno where status = 'P' ORDER BY `idAluno` DESC";
			
			statement = conexao.createStatement();
			resultSet = statement.executeQuery(query);
			
			alunos = new ArrayList<Aluno>();
			
			while(resultSet.next()){
				aluno = new Aluno();
				
				aluno.setIdAluno(resultSet.getInt("idAluno"));
				aluno.setNomeAluno(resultSet.getString("nomeAluno"));
				aluno.setSexoAluno(resultSet.getString("sexoAluno"));
				aluno.setDobAluno(resultSet.getDate("dobAluno"));
				aluno.setOmAluno(resultSet.getString("omAluno"));
				aluno.setNomePai(resultSet.getString("nomePai"));
				aluno.setNomeMae(resultSet.getString("nomeMae"));
				aluno.setIdCliente(resultSet.getInt("Cliente_idCliente"));
				aluno.setStatus(resultSet.getString("status"));
				
				alunos.add(aluno);
			}
			
		}catch(SQLException erro){
			System.out.println(erro.getMessage());
		}
		
		try {
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.out.println("\n Erro ao fechar o objeto preparedStatement.");
		}
		System.out.println("\nSucesso pesquisa alunos P");
		return alunos;
		
	}

}
